package com.sxfq.loansupermarket.backsystem.modular.marketing.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.DateUtil;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.PayServicedInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.SearchReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.StoreRecordsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.TongJiInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IStoreRecordsService;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.AddVisitedInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.ConsultantInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.service.IConsultantService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @author py
 * @Date
 */
@Controller
@RequestMapping("/storeRecords")
public class StoreRecordsController extends BaseController {
    @Autowired
    IStoreRecordsService storeRecordsService;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    IReservationsService reservationsService;

    @Autowired
    IConsultantService service;

    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/marketing/reservations/";

    /**
     * 市场部预约管理 已到 列表
     */
    @RequestMapping("/list")
    @ResponseBody
    public Object listYD(SearchReservationsInfo info, HttpServletRequest request) {
        Page<ReservationsInfo> page = new PageFactory<ReservationsInfo>().defaultPage();
        try {
            info.setStartIndex(page.getSize() * (page.getCurrent() - 1));
            info.setLimitIndex(page.getSize());
            page.setCurrent(0);
            List<ReservationsInfo> list = storeRecordsService.selectPageListByStatusMapYDMZ(page, info);
            if (list.size() == 0) {
                info.setStartIndex(0);
                list = storeRecordsService.selectPageListByStatusMapYDMZ(page, info);
            }
            page.setRecords(list);
            page.setTotal(storeRecordsService.getQueryPagesCountYDMZ(page, info));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }

    /**
     * 查询客户信息
     */
    @RequestMapping(value = "/getReservationsByReach", method = RequestMethod.POST)
    @ResponseBody
    public StoreRecordsInfo getCustomerByReach(String reach, String pageStart, String pageSize, Model model) {
        String sql2 = " ";
        if (StringUtils.isNotBlank(reach)) {
            sql2 = "  and t.search_field like CONCAT('%','"+reach+"','%') ";
        }
        String sql = "select * from (select " +
                "     (" +
                "      CASE t.customer_sex" +
                "      WHEN 1 THEN" +
                "      '男' " +
                "      WHEN 2 THEN " +
                "      '女' " +
                "      ELSE " +
                "      '未知' " +
                "      END " +
                "      ) as sex , t.id,t.customer_name as customerName,t.one_channel as oneChannel," +
                "t.department as department,t.customer_age as customerAge,t.customer_sex_head as customerSexHead,t.first_visit_date as firstVisitDate," +
                "t.customer_phone as customerPhone,t.customer_phone2 as customerPhone2,t.status,t.arrival_store as arrivalStore," +
                "t.phone_shar_ship as phoneSharShip from reservations_info t " +  
                " where 1=1 " + sql2+" ) t2 group by t2.id order by t2.firstVisitDate " +
                " limit 0,"+Integer.parseInt(pageStart)*Integer.parseInt(pageSize)+"";
        List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql, CustomerInfo.class);
        String sql1 = "select count(1) from (select id from reservations_info t where 1=1 " + sql2+ " group by id) t";
        Integer count = sqlMapper.selectOne(sql1, Integer.class);
        StoreRecordsInfo info=new StoreRecordsInfo();
        info.setCount(count);
        info.setCustomerInfoList(customerInfoList);
        return info;
    }


    //已到导出
    @RequestMapping(value = "/reservationsExportYDMZ")
    public void reservationsExportYDMZ(@Valid SearchReservationsInfo searchInfo, HttpServletResponse response) {
        try {
            List<ReservationsInfo> list = storeRecordsService.reservationsExportYDMZ(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 26; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("预约号");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("标识号");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("客户姓名");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("手机号码");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("初诊门诊");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("建档人");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("跟进人");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("归属人");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("部门");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("建档时间");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("预约时间");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("回访状态");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("到诊时间");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("一级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("二级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("三级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("一级媒介");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("二级媒介");
            cell.setCellStyle(style);
            cell = row.createCell(18);
            cell.setCellValue("三级媒介");
            cell.setCellStyle(style);
            cell = row.createCell(19);
            cell.setCellValue("回访");
            cell.setCellStyle(style);
            cell = row.createCell(20);
            cell.setCellValue("性别");
            cell.setCellStyle(style);
            cell = row.createCell(21);
            cell.setCellValue("年龄");
            cell.setCellStyle(style);
            cell = row.createCell(22);
            cell.setCellValue("开单状态");
            cell.setCellStyle(style);
            cell = row.createCell(23);
            cell.setCellValue("成交金额");
            cell.setCellStyle(style);
            cell = row.createCell(24);
            cell.setCellValue("预约病种");
            cell.setCellStyle(style);
            cell = row.createCell(25);
            cell.setCellValue("访问病种");
            cell.setCellStyle(style);
            cell = row.createCell(26);
            cell.setCellValue("消费项目");
            cell.setCellStyle(style);
            cell = row.createCell(27);
            cell.setCellValue("初诊TC");
            cell.setCellStyle(style);
            cell = row.createCell(28);
            cell.setCellValue("关键词");
            cell.setCellStyle(style);
            cell = row.createCell(29);
            cell.setCellValue("微信号");
            cell.setCellStyle(style);

            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                ReservationsInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 29; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(map.getReservationsNum());
                row.getCell(1).setCellValue(map.getIdentificationNumber());
                row.getCell(2).setCellValue(map.getCustomerName());
                if(Integer.parseInt(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"PhonePrivilegesexp",0)+"")==1) {
                    row.getCell(3).setCellValue(map.getCustomerPhone());
                }else{
                    row.getCell(3).setCellValue("无");
                }
                row.getCell(4).setCellValue(map.getArrivalStore());
                row.getCell(5).setCellValue(map.getArchiving());
                row.getCell(6).setCellValue(map.getFollower());
                row.getCell(7).setCellValue(map.getBelonger());
                row.getCell(8).setCellValue(map.getDepartment());
                row.getCell(9).setCellValue(map.getFilingTime());
                row.getCell(10).setCellValue(map.getAppointmentTime());
                row.getCell(11).setCellValue(map.getStatuss());
                row.getCell(12).setCellValue(map.getFirstVisitDate());
                row.getCell(13).setCellValue(map.getOneChannel());
                row.getCell(14).setCellValue(map.getTwoChannel());
                row.getCell(15).setCellValue(map.getThrChannel());
                row.getCell(16).setCellValue(map.getOneMedium());
                row.getCell(17).setCellValue(map.getTwoMedium());
                row.getCell(18).setCellValue(map.getThrMedium());
                row.getCell(19).setCellValue(map.getHuifang());
                row.getCell(20).setCellValue(map.getSex());
                row.getCell(21).setCellValue(map.getCustomerAge());
                row.getCell(22).setCellValue(map.getDealStatus());
                row.getCell(23).setCellValue(map.getMoney());
                row.getCell(24).setCellValue(map.getAppointmentPayService());
                row.getCell(25).setCellValue(map.getAccessDisease());
                row.getCell(26).setCellValue(map.getExpenditureProject());
                row.getCell(27).setCellValue(map.getTcName());
                row.getCell(28).setCellValue(map.getKeyWord());
                row.getCell(29).setCellValue(map.getWechatNumber());
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 已到记录统计查询
     */
    @RequestMapping(value = "/tongjiydjl", method = RequestMethod.GET)
    @ResponseBody
    public List<TongJiInfo> tongjiydjl(String param1, String param2, String param3, String param4, String param5,
                                       String param6, String param7, String param8, String param9, String param10,
                                       String param11, String param12, String param13, String param14, String param15,
                                       String param16, String param17, String param18) throws Exception {
        //1.获取当前日期，格式为yyyy-MM-dd hh:mm:ss
        String currentDate = DateUtil.getTime(new Date()).substring(0, 10);//取今天的年月日
        //获取昨天日期字符串 格式为yyyy-MM-dd hh:mm:ss
        String lastDate = DateUtil.getAfterDayDate("-1").substring(0, 10);//取昨日 的年月日
        //获取当月的字符串
        String currerntMonth = currentDate.substring(0, 7);//yyyy-mm
        //获取上月日期
        String lastMonth = DateUtil.getAfterDayMonth("-1").substring(0, 7);//取上月的年月
        String contitionInfo = "";
        if (StringUtil.isNotEmpty(param1) && !"请选择".equals(param1)) {
            String arrivalStore = "";
            String arrivalStore2[] = param1.split(",");
            if (arrivalStore2.length == 1) {
                arrivalStore = arrivalStore2[0];
            } else {
                for (int i = 0; i < arrivalStore2.length; i++) {
                    if (i == (arrivalStore2.length - 1)) {
                        arrivalStore += "'" + arrivalStore2[i];
                    } else if (i == 0) {
                        arrivalStore += arrivalStore2[i] + "'" + ",";
                    } else {
                        arrivalStore += "'" + arrivalStore2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.arrival_store in ( '" + arrivalStore + "') ";
        }
        if (StringUtils.isNotBlank(param2)) {
            contitionInfo += " and t.belonger in (select t1.account from user t1 where t1.roleid='"+param2+"') ";
        }
        if (StringUtils.isNotBlank(param3)) {
            contitionInfo += " and t.archiving= '" + param3 + "' ";
        }
        if (StringUtils.isNotBlank(param4)) {
            if (!"请选择".equals(param4)) {
                contitionInfo += " and t.thr_channel= '" + param4 + "' ";
            }
        }
        if (StringUtils.isNotBlank(param5)) {
            if (!"请选择".equals(param5)) {
                contitionInfo += " and t.two_channel= '" + param5 + "' ";
            }
        }
        if (StringUtils.isNotBlank(param6)) {
            if (!"请选择".equals(param6)) {
                contitionInfo += " and t.one_channel= '" + param6 + "' ";
            }
        }
        if (StringUtil.isNotEmpty(param14) && !"请选择".equals(param14)) {
            String area = "";
            String area2[] = param14.split(",");
            if (area2.length == 1) {
                area = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area += "'" + area2[i];
                    } else if (i == 0) {
                        area += area2[i] + "'" + ",";
                    } else {
                        area += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.arrival_store in (select a.simplename from dept a where a.area_abbreviation in ('" + area + "')) ";
        }
        if (StringUtils.isNotBlank(param16)) {
            if (!"请选择".equals(param16)) {
                contitionInfo += " and t.one_medium= '" + param16 + "' ";
            }
        }
        if (StringUtils.isNotBlank(param17)) {
            if (!"请选择".equals(param17)) {
                contitionInfo += " and t.two_medium= '" + param17 + "' ";
            }
        }
        if (StringUtils.isNotBlank(param18)) {
            if (!"请选择".equals(param18)) {
                contitionInfo += " and t.thr_medium= '" + param18 + "' ";
            }
        }
        if (StringUtil.isNotEmpty(param7) && !"请选择".equals(param7)) {
            String statuss = "";
            String statuss2[] = param7.split(",");
            if (statuss2.length == 1) {
                statuss = statuss2[0];
            } else {
                for (int i = 0; i < statuss2.length; i++) {
                    if (i == (statuss2.length - 1)) {
                        statuss += "'" + statuss2[i];
                    } else if (i == 0) {
                        statuss += statuss2[i] + "'" + ",";
                    } else {
                        statuss += "'" + statuss2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.statuss in ( '" + statuss + "') ";
        }
        if (StringUtils.isNotBlank(param8)) {
            contitionInfo += " and t.access_disease= '" + param8 + "' ";
        }
        if (StringUtils.isNotBlank(param9)) {
            contitionInfo += " and t.appointmentPayService= '" + param9 + "' ";
        }
        if (StringUtils.isNotBlank(param10)) {
            contitionInfo += " and t.follower= '" + param10 + "' ";
        }
        if (StringUtils.isNotBlank(param11)) {
            contitionInfo += " and t.belonger= '" + param11 + "' ";
        }
        //姓名   电话   微信  预约号
        if (StringUtils.isNotBlank(param15)) {
            contitionInfo += " and (t.customer_name like CONCAT('%"+param15+"%') or t.customer_phone like CONCAT('%"+param15+"%') " +
                    " or t.customer_phone2 like CONCAT('%"+param15+"%') " +
                    " or t.wechat_number like CONCAT('%"+param15+"%') or t.reservations_num like CONCAT('%"+param15+"%') " +
                    " or t.identification_number like CONCAT('%"+param15+"%') or t.key_word like CONCAT('%"+param15+"%') " +
                    " or t.szm like CONCAT('%"+param15+"%')) ";
        }
        contitionInfo += "  group by t.id) t ";
        //查询今日已到
        String sql = "select count(*) as countNum from (select id from reservations_info t where left(first_visit_date,10) = '" + currentDate + "' and t.status ='到店' " + contitionInfo +
                //查询昨日已到
                " union all select count(*) as countNum from (select id from reservations_info t where left(first_visit_date,10) = '" + lastDate + "' and t.status ='到店' " + contitionInfo +
                //查询本周已到
                " union all select count(*) as countNum from (select id from reservations_info t where first_visit_date >='" + param12 + "' and first_visit_date <='" + param13 + "' and t.status ='到店' " + contitionInfo +
                //查询当月已到
                " union all select count(*) as countNum from (select id from reservations_info t where left(first_visit_date,7) = '" + currerntMonth + "' and t.status ='到店' " + contitionInfo +
                //查询上月已到
                " union all select count(*) as countNum from (select id from reservations_info t where left(first_visit_date,7) = '" + lastMonth + "' and t.status ='到店' " + contitionInfo;
        List<TongJiInfo> tongJiInfoList = sqlMapper.selectList(sql, TongJiInfo.class);
        return tongJiInfoList;
    }

    //已到记录统计金额
    @RequestMapping("/accountjl")
    @ResponseBody
    public HashMap accountjl(@RequestParam(required = false) String reach,
                             @RequestParam(required = false) String area,
                             @RequestParam(required = false) String appointmentPayService,
                             @RequestParam(required = false) String archiving,
                             @RequestParam(required = false) String accessDisease,
                             @RequestParam(required = false) String startFirstVisitDate,
                             @RequestParam(required = false) String endFirstVisitDate,
                             @RequestParam(required = false) String outpatientName,
                             @RequestParam(required = false) String oneChannel, @RequestParam(required = false) String status,
                             @RequestParam(required = false) String twoChannel, @RequestParam(required = false) String thrChannel,
                             @RequestParam(required = false) String statuss,
                             @RequestParam(required = false) String department, HttpServletResponse response) {
        String sql = "select sum(t.money) as totalMoney " +
                " from reservations_info t  " +
                "where t.status ='到店'";
        if (StringUtil.isNotEmpty(reach)) {
            sql += " and (t.customer_name like CONCAT('%','" + reach + "','%') or t.customer_phone like CONCAT('%','" + reach + "','%') or t.wechat_number like CONCAT('%','" + reach + "','%') or t.reservations_num like CONCAT('%','" + reach + "','%') or t.szm like CONCAT('%','" + reach + "','%') or t.customer_phone2 like CONCAT('%','" + reach + "','%')) ";
        }
        if (StringUtil.isNotEmpty(appointmentPayService)) {
            sql += " and t.appointment_pay_service = '" + appointmentPayService + "'";
        }
        if (StringUtil.isNotEmpty(archiving)) {
            sql += " and t.archiving = '" + archiving + "'";
        }
        if (StringUtil.isNotEmpty(area) && !"请选择".equals(area)) {
            String area1 = "";
            String area2[] = area.split(",");
            if (area2.length == 1) {
                area1 = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area1 += "'" + area2[i];
                    } else if (i == 0) {
                        area1 += area2[i] + "'" + ",";
                    } else {
                        area1 += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            sql += " and t.arrival_store in (select a.simplename from dept a where a.area_abbreviation in ('" + area1 + "')) ";
        }
        if (StringUtil.isNotEmpty(accessDisease)) {
            sql += " and t.access_disease = '" + accessDisease + "'";
        }
        if (StringUtil.isNotEmpty(startFirstVisitDate)) {
            sql += " and t.first_visit_date  >= '" + startFirstVisitDate + "'";
        }
        if (StringUtil.isNotEmpty(endFirstVisitDate)) {
            sql += " and t.first_visit_date  <= '" + endFirstVisitDate + "'";
        }
        if (StringUtil.isNotEmpty(outpatientName)) {
            sql += " and t.outpatient_name  = '" + outpatientName + "'";
        }
        if (StringUtil.isNotEmpty(oneChannel) && !oneChannel.equals("请选择")) {
            sql += " and t.one_channel  = '" + oneChannel + "'";
        }
        if (StringUtil.isNotEmpty(twoChannel) && !twoChannel.equals("请选择")) {
            sql += " and t.two_channel  = '" + twoChannel + "'";
        }
        if (StringUtil.isNotEmpty(thrChannel) && !thrChannel.equals("请选择")) {
            sql += " and t.thr_channel  = '" + thrChannel + "'";
        }
        if (StringUtil.isNotEmpty(outpatientName)) {
            sql += " and t.outpatient_name  = '" + outpatientName + "'";
        }
        if (StringUtil.isNotEmpty(department)) {
            sql += " and t.belonger in (select t1.account from user t1 where t1.roleid='" + department + "')";
        }
        if (StringUtil.isNotEmpty(status)) {
            sql += " and t.status  = '" + status + "'";
        }
        Map<String, Object> result = sqlMapper.selectOne(sql.toString());
        HashMap map = new HashMap();
        if (result != null) {
            map.put("totalMoney", result.get("totalMoney"));
        } else {
            map.put("totalMoney", "0");
        }
        return map;
    }

    /**
     * 已到 点击姓名跳转客户管理详情页面  （需要获取到成交记录   和  看诊记录的数据）
     */
    @RequestMapping(value = "/reservations_arrive/{id}")
    public String to_reservationsNotDetail(@PathVariable String id, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(id)) {
            return "/errorClose.html";
        }
        try {
            String sql1 = "select table_name from information_schema.tables where table_schema='crmscs' " +
                    "and table_name like '%customer_info%'";
            List<ReservationsInfo> list1 = sqlMapper.selectList(sql1, ReservationsInfo.class);
            String tableHead1 = list1.get(0).getTableName().substring(0, list1.get(0).getTableName().length() - 13);
            String sql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ," +
                    "t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ," +
                    "t1.pay_service_num as payServiceNum , t1.service_actual_pay as serviceActualPay ," +
                    "sum(t1.service_actual_pay) as totleServiceActualPay," +
                    "(select sum(folding_pay) from " + tableHead1 + "deal_info a where a.operation_time=t1.operation_time and a.operation_time=a.get_time) as totleFoldingPay," +
                    "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                    " group_concat(distinct t1.doctor) as doctor," +
                    "left(t1.operation_time,19) as operationTime, t1.folding_pay as foldingPay," +
                    "t1.pay_service as payService,t2.id as payServiceId," +
                    "t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                    "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, " +
                    "t2.pay_service_price_two as payServicePriceTwo," +
                    "t3.customer_name as customerName,t3.reservations_id as reservationsId,t3.medical_record_number as medicalRecordNumber," +
                    "( " +
                    "      CASE t3.customer_sex " +
                    "      WHEN 1 THEN " +
                    "          '男' " +
                    "      WHEN 2 THEN " +
                    "          '女' " +
                    "      ELSE " +
                    "          '未知' " +
                    "      END " +
                    "    ) as sex  ,t3.customer_age as customerAge,t3.customer_phone as customerPhone ,t3.field_tc as fieldTc  " +
                    " ,left(t1.get_time,19) as getTime from " + tableHead1 + "deal_info t1 left join "+redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_info",0)+" t2 " +
                    " on t1.pay_service_id=t2.id left join " + list1.get(0).getTableName() + " t3 on t1.customer_id=t3.id " +
                    " where t1.reservations_id='" + id + "' and pay_service_id is not null  and t1.deal_status ='已结账' " +
                    " group by  operationTime ";
            for (int i = 1; i < list1.size(); i++) {
                tableHead1 = list1.get(i).getTableName().substring(0, list1.get(i).getTableName().length() - 13);
                sql += " union all select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ," +
                        "t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ," +
                        "t1.pay_service_num as payServiceNum , t1.service_actual_pay as serviceActualPay ," +
                        "sum(t1.service_actual_pay) as totleServiceActualPay," +
                        "(select sum(folding_pay) from " + tableHead1 + "deal_info a where a.operation_time=t1.operation_time and a.operation_time=a.get_time) as totleFoldingPay," +
                        "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                        " group_concat(distinct t1.doctor) as doctor," +
                        "left(t1.operation_time,19) as operationTime,t1.folding_pay as foldingPay," +
                        "t1.pay_service as payService,t2.id as payServiceId," +
                        "t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                        "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, " +
                        "t2.pay_service_price_two as payServicePriceTwo," +
                        "t3.customer_name as customerName,t3.reservations_id as reservationsId,t3.medical_record_number as medicalRecordNumber," +
                        "( " +
                        "      CASE t3.customer_sex " +
                        "      WHEN 1 THEN " +
                        "          '男' " +
                        "      WHEN 2 THEN " +
                        "          '女' " +
                        "      ELSE " +
                        "          '未知' " +
                        "      END " +
                        "    ) as sex  ,t3.customer_age as customerAge,t3.customer_phone as customerPhone ,t3.field_tc as fieldTc  " +
                        " ,left(t1.get_time,19) as getTime from " + tableHead1 + "deal_info t1 left join "+redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_info",0)+" t2 on " +
                        " t1.pay_service_id=t2.id left join " + list1.get(i).getTableName() + " t3 on t1.customer_id=t3.id " +
                        " where t1.reservations_id='" + id + "' and pay_service_id is not null and t1.deal_status ='已结账' " +
                        " group by  operationTime ";
            }
            sql += " order by operationTime desc";
            List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
            String getTime = "";
            for (PayServicedInfo info : payServiceInfoList) {
                if (info != null) {
                    if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                        info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                        info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                    }
                    getTime = info.getGetTime();
                }
            }
            List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList(id);
            for (ConsultantInfo infos : consultantInfoList) {
                if (infos.getDiagnosisTime() != "" && infos.getDiagnosisTime() != null) {
                    infos.setDiagnosisTimeOne(infos.getDiagnosisTime().substring(0, 10));
                    infos.setDiagnosisTimeTwo(infos.getDiagnosisTime().substring(11, infos.getDiagnosisTime().length()));
                }
            }
            String tcName = "";
            String firstVisitDate = "";
            String sql3 = "select id, tc_name as tcName,first_visit_date as firstVisitDate from reservations_infosc a where id= '" + id + "' limit 0,1";
            ReservationsInfo reservationsInfo1 = sqlMapper.selectOne(sql3, ReservationsInfo.class);
            if (reservationsInfo1 != null) {
                tcName = reservationsInfo1.getTcName();
                firstVisitDate = reservationsInfo1.getFirstVisitDate();
            }
            //成交记录数据返回模型视图
            model.addAttribute("payServiceInfoList", payServiceInfoList);
            ReservationsInfo reservationsInfo = this.reservationsService.selectOneById(id);
            model.addAttribute("userName", ShiroKit.getUser().getAccount());
            model.addAttribute("reservationsId", id);
            model.addAttribute("reservationsInfo", reservationsInfo);
            model.addAttribute("getTime", getTime);
            List<AddVisitedInfo> revisitRecordList = service.getRevisitRecordList(id);
            model.addAttribute("revisitRecordList", revisitRecordList);
            model.addAttribute("consultantInfoList", consultantInfoList);
            model.addAttribute("tcName", tcName);
            model.addAttribute("firstVisitDate", firstVisitDate);
            return PREFIX + "reservationsReach.html";
        } catch (Exception e){
            return "/errorClose.html";
        }
    }
}